/*
 * DAO for DVD queries.
 */
package com.cheapflix.dao;

import com.cheapflix.beans.DVD;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * DAO class that has DVD search functionalities.
 * @author  Group B
 */
public class DVDDao extends AbstractDAO
{

    private static final Logger logger = Logger.getLogger(DVDDao.class);

    @Autowired
    public void init(DataSource ds)
    {
        logger.info("Datasource: " +ds);
        super.init(ds);
    }

    /**
     * Maps a result set to a DVD bean.
     * @param rs - results of a query
     * @return The DVD matching the results.
     * @throws SQLException
     */
    public static DVD ResultSetToDVD(ResultSet rs) throws SQLException
    {
        DVD dvd = new DVD();
        dvd.setId(rs.getInt("id"));
        dvd.setActors(rs.getString("actors"));
        dvd.setGenre(rs.getString("genre"));
        dvd.setImage(rs.getString("image"));
        dvd.setTitle(rs.getString("title"));
        dvd.setDirector(rs.getString("director"));
        dvd.setYear(rs.getString("year"));
        dvd.setRuntime(rs.getString("runtime"));
        return dvd;
    }

    /**
     * Finds a DVD matching the specified column.
     * @param column - DVD column
     * @param prefix - Search criteria.
     * @return List of DVDs
     */
    public List<DVD> getDVDsByColumnPrefix(String column, String prefix)
    {
        String sql = "select * from cheapflix.dvd where " + column + " like '%" + prefix + "%'";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<DVD> results = new ArrayList<DVD>();
        try
        {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.debug("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next())
            {
                results.add(ResultSetToDVD(rs));
            }
            return results;
        }
        catch (SQLException ex)
        {
            logger.error(ex.getMessage(), ex);
            return null;
        }
        finally
        {
            close(stmt, rs);
        }
    }

    /**
     * Returns DVDs matching a title.
     * @param prefix - Title search string
     * @return List of DVDs
     */
    public List<DVD> getDVDsByTitlePrefix(String prefix)
    {
        return getDVDsByColumnPrefix("title", prefix);
    }

    /**
     * Returns DVDs matching a genre
     * @param prefix - Genre search string.
     * @return List of DVDs
     */
    public List<DVD> getDVDsByGenre(String prefix)
    {
        return getDVDsByColumnPrefix("genre", prefix);
    }

    /**
     * DVDs with an actor
     * @param prefix - Actor in a DVD
     * @return List of DVDs
     */
    public List<DVD> getDVDsByActors(String prefix)
    {
        return getDVDsByColumnPrefix("actors", prefix);
    }

    /**
     * DVDs with a director
     * @param prefix - Director in a DVD.
     * @return List of DVDs
     */
    public List<DVD> getDVDsByDirector(String prefix)
    {
        return getDVDsByColumnPrefix("director", prefix);
    }

    /**
     * DVD matching an ID.
     * @param dvdId
     * @return DVDs
     */
    public DVD getDVD(int dvdId)
    {
        String sql = "select * from dvd where id=" + dvdId;

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try
        {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.debug("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            DVD dvd = null;
            if (rs.first()) {
                dvd = ResultSetToDVD(rs);
            }
            return dvd;
        }
        catch (SQLException ex)
        {
            logger.error(ex.getMessage(), ex);
            return null;
        }
        finally
        {
            close(conn,stmt, rs);
        }
    }

    /**
     * Searches for DVDs with specific fields
     * @param title
     * @param genre
     * @param actors
     * @param director
     * @return List of DVDs matching search criteria
     */
    public List<DVD> findDvds(final String title, final String genre,
            final String actors, final String director) {
        Map<String, String> map = new HashMap<String, String>();
        if (!title.isEmpty()) {
            map.put("title", title);
        }
        if (!genre.isEmpty()) {
            map.put("genre", genre);
        }
        if (!actors.isEmpty()) {
            map.put("actors", actors);
        }
        if (!director.isEmpty()) {
            map.put("director", director);
        }
        return findDvds(map, true);
    }

    /**
     * Searches across all fields
     * @param criteria - general search criteria
     * @return List of DVDs with a matching field.
     */
    public List<DVD> findDvds(final String criteria) {
        Map<String, String> map = new HashMap<String, String>();
        map.put("title", criteria);
        map.put("genre", criteria);
        map.put("actors", criteria);
        map.put("director", criteria);
        return findDvds(map, false);
    }

    /**
     * Searches based on the columns and criteria in the map.
     * @param columnSearchMap - Map of column and search criteria
     * @param isUnion uses and clause if true, or clause otherwise
     * @return List of DVDs with a matching field.
     */
    private List<DVD> findDvds(
            final Map<String, String> columnSearchMap, final boolean isUnion) {
        List<DVD> dvds = new ArrayList<DVD>();
        String operand = " or ";
        if (isUnion) {
            operand = " and ";
        }
        String sql = "select * from cheapflix.dvd where ";
        Iterator<Map.Entry<String, String>> iter =
                columnSearchMap.entrySet().iterator();
        if (iter.hasNext()) {
            Map.Entry<String, String> entry = iter.next();
            sql += entry.getKey() + " like '%" + entry.getValue() + "%'";
        }
        while (iter.hasNext()) {
            Map.Entry<String, String> entry = iter.next();
            sql += operand + entry.getKey() + " like '%" + entry.getValue() + "%'";
        }
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try
        {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.debug("sql: " + sql);
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                dvds.add(ResultSetToDVD(rs));
            }
            return dvds;

        }
        catch (SQLException ex)
        {
            logger.error(ex.getMessage(), ex);
            return null;
        }
        finally
        {
            close(stmt, rs);
        }
    }
}
